package com.ars.common.dao.UserInfo;

import com.ars.common.beans.User.User;
import com.ars.common.utils.DBUtil;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;

public class getClassUserInfo {

    public static ArrayList<User> getUser(String classID) throws SQLException
    {
       String sql = "-- 左连接查询\n" +
               "SELECT u.userID, u.mailbox, u.name, u.phone, s.sex, r.role, a.AdminGrade, a.id AS AdminID, u.password\n" +
               "FROM user u\n" +
               "LEFT JOIN role r ON u.role = r.id\n" +
               "LEFT JOIN sex s ON u.sex = s.id\n" +
               "LEFT JOIN admin a ON u.isAdmin = a.id\n" +
               "where u.userID like ?" +
               "LIMIT 0, 1000;\n ";


        PreparedStatement  ps = DBUtil.getConnection().prepareStatement(sql);
        ps.setString(1,classID+"%");

        ResultSet rs = ps.executeQuery();
        ArrayList<User> users = new ArrayList<User>();
        while(rs.next())
        {
            User user = new User();
            user.setUserID(rs.getString("userID"));
            user.setPassword(rs.getString("password"));
            user.setMailbox(rs.getString("mailbox"));
            user.setName(rs.getString("name"));
            user.setPhone(rs.getString("phone"));
            user.setSex(rs.getString("sex"));
            user.setRole(rs.getString("role"));
            user.setAdminID(rs.getString("AdminID"));//管理员等级编号
            user.setAdminName(rs.getString("AdminGrade"));//管理员等级
            users.add(user);
        }
        return users;
    }
}
